package com.app.miwawa.ext.util.file;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.framework.common.ext.util.DateUtil;
import org.framework.common.ext.util.PropertiesUtil;
import org.framework.common.ext.util.StringUtil;
import org.framework.common.ext.util.file.FileUtil.FileType;

public class ExcelUtil {
	
	private static int READ_ROW_INDEX_TITLE = 1;
	private static int READ_ROW_INDEX_DATAS = 2;
	private static int READ_COLUMN_INDEX_BEGING = 1;
	private static int RAED_COLUMN_INDEX_FINIFH = 20;
	private static String IMPORT_EXECUTE_SQL = "";
	
	private static final String CONFIG_PATH = "/config/properties/excel.sheet[0].properties";
	private static final String CONFIG_IMPORT_EXECUTE_SQL = "import.execute.sql";
	private static final String CONFIG_PROPERTY_READ_ROW_INDEX_TITLE = "read.row.index.title";
	private static final String CONFIG_PROPERTY_READ_ROW_INDEX_DATAS = "read.row.index.datas";
	private static final String CONFIG_PROPERTY_READ_COLUMN_INDEX_BEGING = "read.column.index.beging";
	private static final String CONFIG_PROPERTY_RAED_COLUMN_INDEX_FINIFH = "read.column.index.finish";
	
	private	static String[] READ_EXCLUDE_NAMES = new String[]{};
	private static final String CONFIG_PROPERTY_READ_EXCLUDE_NAMES = "read.exclude.names";

	/**
	 * 回调接口.
	 * <p>描述:</p>
	 * <p>Company:上海中信信息发展股份有限公司</p>
	 * @author chanlong(陈龙)
	 * @date 2015年10月2日  下午12:54:18
	 * @version 1.0.2015.
	 */
	public static interface Callback{
		public void function(Map<String,Object> content, int index) throws Exception;
	}
	
	public static String sql(){
		return IMPORT_EXECUTE_SQL;
	}
	
	public static String[] readExcelTitle(Sheet sheet) {
        List<String> titles = new ArrayList<String>();
        try {
	        Row row = sheet.getRow(READ_ROW_INDEX_TITLE);
	        for (int i = READ_COLUMN_INDEX_BEGING; i <= RAED_COLUMN_INDEX_FINIFH; i++) {
	        	String title = getCellFormatValue(row.getCell(i)).trim();
	        	titles.add(config.getProperty(title));
	        }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titles.toArray(new String[]{});
    }
	
	/**
	 * 读取Excel数据内容.
	 * @param input
	 * @param sheetIndex
	 * @param excludes 排除的值，与配置文件中的排除项对应
	 * @return
	 * @author chanlong(陈龙)
	 * @date 2015年9月29日  下午4:40:36
	 */
    public static List<Map<String, String>> readExcelContent(final String extensions, InputStream input, int sheetIndex, String... excludes) {
    	List<Map<String, String>> data = new ArrayList<Map<String,String>>();
        try {
        	Workbook book = null;
        	if(FileType.xls.name().equals(extensions)){
        		book = new HSSFWorkbook(input);
        	}else if(FileType.xlsx.name().equals(extensions)){
        		book = new XSSFWorkbook(input);
        	}
	        Sheet sheet = book.getSheetAt(sheetIndex);
	        
	        // 读取头部标题
	        String[] titles = readExcelTitle(sheet);
	        
	        // 读取正文内容
	        begin:for (int x = READ_ROW_INDEX_DATAS, rows = sheet.getLastRowNum(); x <= rows; x++) {
	            Row row = sheet.getRow(x);
	            Map<String, String> content = new LinkedHashMap<String, String>();
	            loop:for(int i = 0, size = titles.length; i < size; i++){
	            	String value = getCellFormatValue(row.getCell(i + READ_COLUMN_INDEX_BEGING)).trim();
	            	if(i == 0 && StringUtil.isEmpty(value)){
	            		continue begin;
	            	}else if(ArrayUtils.contains(READ_EXCLUDE_NAMES, titles[i]) && !ArrayUtils.contains(excludes, value)){
	            		continue loop;
	            	}else{
	            		content.put(titles[i], value);
	            	}
	            }
	            data.add(content);
	        }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return data;
    }
    
    public static void readExcelContent(final String extensions, final InputStream input, final int sheetIndex, final Callback call, final String... excludes) throws Exception {
    	Workbook book = null;
    	if(FileType.xls.name().equals(extensions)){
    		book = new HSSFWorkbook(input);
    	}else if(FileType.xlsx.name().equals(extensions)){
    		book = new XSSFWorkbook(input);
    	}
        Sheet sheet = book.getSheetAt(sheetIndex);
        
        // 读取头部标题
        String[] titles = readExcelTitle(sheet);
        // System.out.println(ArrayUtils.toString(titles));
        
		// 读取正文内容
		begin: for (int x = READ_ROW_INDEX_DATAS, index = 0, rows = sheet.getLastRowNum(); x <= rows; x++, index++) {
			Row row = sheet.getRow(x);
			Map<String, Object> content = new LinkedHashMap<String, Object>();
			
			loop: for (int i = 0, size = titles.length; i < size; i++) {
				String value = getCellFormatValue(row.getCell(i + READ_COLUMN_INDEX_BEGING)).trim();
				if (i == 0 && StringUtil.isEmpty(value)) {
					continue begin;
				} else if (ArrayUtils.contains(READ_EXCLUDE_NAMES, titles[i]) && !ArrayUtils.contains(excludes, value)) {
					continue loop;
				} else {
					content.put(titles[i], value);
				}
			}

			call.function(content, index);
		}
    }
    
	// 根据XSSFCell类型设置数据.
    private static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            	case XSSFCell.CELL_TYPE_NUMERIC: 
            	case XSSFCell.CELL_TYPE_FORMULA: {
            		try{
    	                // 判断当前的cell是否为Date
    	                if (HSSFDateUtil.isCellDateFormatted(cell)) {
    	                    // 如果是Date类型则，转化为Date格式
    	                    Date date = cell.getDateCellValue();
    	                    cellvalue = DateFormatUtils.format(date, DateUtil.DatePattern.DATE.value());
    	                }
    	                // 如果当前Cell的Type为NUMERIC
    	                else{
    	                	cellvalue =  new BigDecimal(cell.getNumericCellValue()).toPlainString();
    	                }
            		}catch(IllegalStateException e){
            			RichTextString value = cell.getRichStringCellValue();
    	                cellvalue =  value != null ? value.getString() : cell.getStringCellValue();
            		}
	                break;
            	}
	            // 如果当前Cell的Type为STRING
	            case XSSFCell.CELL_TYPE_STRING:{
	                // 取得当前的Cell字符串
	            	RichTextString value = cell.getRichStringCellValue();
	                cellvalue =  value != null ? value.getString() : cell.getStringCellValue();
	                break;
	            }
	            // 默认的Cell值
	            default: cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
    
    // 读取excel导入规则的配置文件
    private static PropertiesUtil config;
    
	public static void readExcelImportConfig(final String path){
		if(StringUtil.isNotEmpty(path)){
			config = PropertiesUtil.loader(path);
		}else{
			config = PropertiesUtil.loader(CONFIG_PATH);
		}
		
		IMPORT_EXECUTE_SQL = config.getProperty(CONFIG_IMPORT_EXECUTE_SQL);
		READ_EXCLUDE_NAMES = config.getProperty(CONFIG_PROPERTY_READ_EXCLUDE_NAMES).split(",");
		READ_ROW_INDEX_TITLE = Integer.valueOf(config.getProperty(CONFIG_PROPERTY_READ_ROW_INDEX_TITLE));
		READ_ROW_INDEX_DATAS = Integer.valueOf(config.getProperty(CONFIG_PROPERTY_READ_ROW_INDEX_DATAS));
		READ_COLUMN_INDEX_BEGING = Integer.valueOf(config.getProperty(CONFIG_PROPERTY_READ_COLUMN_INDEX_BEGING));
		RAED_COLUMN_INDEX_FINIFH = Integer.valueOf(config.getProperty(CONFIG_PROPERTY_RAED_COLUMN_INDEX_FINIFH));
	}
	
    public static void main(String[] args) {
    	
	}
}
